using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Portal.Core.DAL;

namespace Portal.BO.Editoral.TK_NhuanBut
{
    public static class NhuanButHelper
    {
        public static DataTable GetList(string fromDate, string toDate, string writer, string news_title, string cat_id, int PageSize, int StartRow)
        {
            
            string strWhereSQL = getSQLWhere(fromDate, toDate, writer, news_title, cat_id);

            DataTable dt = new DataTable();
            using (MainDB objDb = new MainDB())
            {
                dt = (DataTable)objDb.CallStoredProcedure("GetList_NhuanBut", new object[] { strWhereSQL, StartRow, PageSize }, new string[] { "@strWhere", "@StartIndex", "@PageSize" }, true);
            }

            return dt;
        }

        public static int GetNumberRecord(string fromDate, string toDate, string writer, string news_title, string cat_id)
        {
            string strWhereSQL = getSQLWhere(fromDate, toDate, writer, news_title, cat_id);
           

            DataTable dt = new DataTable();
            using (MainDB objDb = new MainDB())
            {
                dt = (DataTable)objDb.CallStoredProcedure("GetList_NhuanBut_NumRow", new object[] { strWhereSQL }, new string[] { "@strWhere" }, true);
            }

            return Convert.ToInt32(dt.Rows[0][0]);
        }

        public static DataTable GetList_NhuanBut_All(string fromDate, string toDate, string writer, string news_title, string cat_id)
        {
            
            string strWhereSQL = getSQLWhere(fromDate, toDate, writer, news_title, cat_id);

            DataTable dt = new DataTable();
            using (MainDB objDb = new MainDB())
            {
                dt = (DataTable)objDb.CallStoredProcedure("GetList_NhuanBut_All", new object[] { strWhereSQL }, new string[] { "@strWhere" }, true);
            }
            return dt;

        }

        private static string getSQLWhere(string fromDate, string toDate, string writer, string news_title, string cat_id)
        {
            string strWhereSQL = "";
            if (!string.IsNullOrEmpty(fromDate))
            {
                if (fromDate.Trim() != "")
                    fromDate = fromDate.Substring(3, 2) + "/" + fromDate.Substring(0, 2) + "/" + fromDate.Substring(6, 4);
                strWhereSQL += " AND News_PublishDate >= '" + fromDate + "'";
            }
            if (!string.IsNullOrEmpty(toDate))
            {
                if (toDate.Trim() != "")
                    toDate = toDate.Substring(3, 2) + "/" + toDate.Substring(0, 2) + "/" + toDate.Substring(6, 4) + " 23:59.999";
                strWhereSQL += " AND News_PublishDate <= '" + toDate + "'";
            }

            if (!string.IsNullOrEmpty(writer) && writer != "-1" && writer != "0")
                strWhereSQL += " AND Extension4 = " + writer + "";
            if (!string.IsNullOrEmpty(news_title))
                strWhereSQL += " AND news_title like N'%" + news_title + "%'";
            if (!string.IsNullOrEmpty(cat_id) && cat_id != "-1" && cat_id != "0")
                strWhereSQL += " AND cat_id = " + cat_id + "";

            return strWhereSQL;
        }
    }
}
